question

WillyB-4093 avatar image
0 Votes"
WillyB-4093 asked MatteoAgostini answered

DAX Formula: Measure Subtotal Issue

I am trying to create a measure that shows the number of stores with zero sales.

Number of Stores = DISTINCTCOUNT(Query[Store_Number])
PP Units = SUM([PP_Units])
Zero Sales Stores = IF([PP_Units]<1,1,BLANK())
% of Stores =DIVIDE([Zero Sales Stores],[Number of Stores],0)
Power Pivot table name = Query

I cannot get any of the measures that I tried to create to add up to 8 in the subtotal for district or territory. Also, I will be adding Digital Service Orders to this table and I will need to count the number of stores without orders. 86354-example.png


power-query-not-supported
example.png (17.7 KiB)
5 |1600 characters needed characters left characters exceeded

Up to 10 attachments (including images) can be used with a maximum of 3.0 MiB each and 30.0 MiB total.

1 Answer

MatteoAgostini avatar image
0 Votes"
MatteoAgostini answered

Dear WillyB-4093,

a tip for the next time; if you want to receive a quick answer to your question add also sample data besides the expected output.
This would help tremendously!

By the way, this is not a power query issue but as you wrote is a missing step in DAX formula and the right place to put this question is here https://community.powerbi.com/t5/Forums/ct-p/PBI_Comm_Forums

I've made some hypotheses recreating your expected output.
93151-willyb-4093.png

The "wrong"/missing part was the following

 Zero Sales Stores = 
 VAR ZeroOrd =
     SUMMARIZE (
         Dim_Store,
         Dim_Store[Store Number],
         "ZeroOrd", IF ( SUMX ( Fact_Orders, Fact_Orders[PP Units] ) < 1, 1, BLANK () )
     )
 RETURN
     SUMX ( ZeroOrd, [ZeroOrd] )

Here you can find a copy of a pbix file where I divided fact and dimension plus a calendar table.
I think it workes smoothly and you can use it for future upgrades.

Let me know what do you think and if I have answered your question!
Have a nice day!
Matteo



willyb-4093.png (41.1 KiB)
5 |1600 characters needed characters left characters exceeded

Up to 10 attachments (including images) can be used with a maximum of 3.0 MiB each and 30.0 MiB total.