Earn the coveted Fabric Analytics Engineer certification. 100% off your exam for a limited time only!
Hi, Thank you in advance for helping me measure for the below sample data.
OrderID | Customer | Y/N? | Class | Amount |
111 | ABC | Y | Apple | 100 |
112 | XYZ | Y | Apple | 120 |
113 | EPM | Y | Apple | 345 |
114 | YRP | N | Orange | 120 |
115 | PS | N | Apple | 452 |
116 | SC | Y | Apple | 150 |
117 | KB | N | Orange | 244 |
118 | RP | N | Mango | 232 |
112 | XYZ | Y | Mango | 440 |
113 | EPM | Y | Mango | 550 |
114 | YRP | N | Apple | 330 |
115 | PS | N | Mango | 348 |
119 | ABC | N | Mango | 546 |
120 | EPM | N | Mango | 550 |
121 | YRP | Y | Orange | 332 |
I tried myself to get the measure in many ways, somehow I am not able to figure it out, please help me. I am able to arrive>>
1. the count of customers, who has "Y and N" = 3 (ABC, EPM, YRP)
2. the total count of orders for them is 6 (111,113,114,119,120 & 121)
But the requirement here is to calculate the count of rows and sum of Amounts for the "Class" from the table for the above mentioned 1 & 2. Expected results are as follows:
Solved! Go to Solution.
Hi,
Here you are:
Measure = SUMX(VALUES('Table'[Customer]), if (CALCULATE(DISTINCTCOUNT('Table'[Y/N?]), ALL('Table'[Class])) = 2,CALCULATE(DISTINCTCOUNT('Table'[OrderID])),BLANK()))
File is attached.
Kind regards, Steve.
Proud to be a Super User!
Awesome Keyboard Shortcusts in Power BI, thumbs up if you like the article
My Community Blog Articles (check them out!)
My Blog - Power M code to automatically detect column types -
How to create test data using DAX!
hi,
then it should be:
Measure = SUMX(SUMMARIZE('Table','Table'[Customer],'Table'[Class]), if (CALCULATE(DISTINCTCOUNT('Table'[Y/N?]), ALL('Table'[Y/N?]), ALL('Table'[Class])) = 2,CALCULATE(DISTINCTCOUNT('Table'[OrderID])),BLANK()))
see attached.
kr, steve.
Proud to be a Super User!
Awesome Keyboard Shortcusts in Power BI, thumbs up if you like the article
My Community Blog Articles (check them out!)
My Blog - Power M code to automatically detect column types -
How to create test data using DAX!
I gues this is what you are looking for:
Measure =
CALCULATE(
SUMX(SUMMARIZE('Table','Table'[Customer],'Table'[Class]), if (CALCULATE(DISTINCTCOUNT('Table'[Y/N?]), ALL('Table'[Y/N?]), ALL('Table'[Class])) = 2,CALCULATE(DISTINCTCOUNT('Table'[OrderID])),BLANK())), 'Table'[Y/N?]="N")
Proud to be a Super User!
Awesome Keyboard Shortcusts in Power BI, thumbs up if you like the article
My Community Blog Articles (check them out!)
My Blog - Power M code to automatically detect column types -
How to create test data using DAX!
Hi,
Here you are:
Measure = SUMX(VALUES('Table'[Customer]), if (CALCULATE(DISTINCTCOUNT('Table'[Y/N?]), ALL('Table'[Class])) = 2,CALCULATE(DISTINCTCOUNT('Table'[OrderID])),BLANK()))
File is attached.
Kind regards, Steve.
Proud to be a Super User!
Awesome Keyboard Shortcusts in Power BI, thumbs up if you like the article
My Community Blog Articles (check them out!)
My Blog - Power M code to automatically detect column types -
How to create test data using DAX!
Thank you, it is really helpful. However, my row counts are not matching to the total. Is possible to match the Totals with row count? Also, I have noticed that there is a gap after "if" in the given measure, May I know the reason and how it works?
Can you send a screenshot or a (partial) file via PM? Difficult to help with your description of the issue alone.
Proud to be a Super User!
Awesome Keyboard Shortcusts in Power BI, thumbs up if you like the article
My Community Blog Articles (check them out!)
My Blog - Power M code to automatically detect column types -
How to create test data using DAX!
Thank you for your quick response, I fetched down the screenshot after applying the given measure in the original data. Columns are the types of "Class" in the sample data. Row total (506) is not matching with the Grand Total(474).
Also, I would like to get a measure for the below:
I calculated the count orders for Class column with your given measure, where [Y/N?] is equal to Y and N and I got the answer as below
Now, I would like to get the orders count from the above measure, where [Y/N?] is equal to "Y" only as like below:
Thanks a lot for supporting me on this.
hi,
then it should be:
Measure = SUMX(SUMMARIZE('Table','Table'[Customer],'Table'[Class]), if (CALCULATE(DISTINCTCOUNT('Table'[Y/N?]), ALL('Table'[Y/N?]), ALL('Table'[Class])) = 2,CALCULATE(DISTINCTCOUNT('Table'[OrderID])),BLANK()))
see attached.
kr, steve.
Proud to be a Super User!
Awesome Keyboard Shortcusts in Power BI, thumbs up if you like the article
My Community Blog Articles (check them out!)
My Blog - Power M code to automatically detect column types -
How to create test data using DAX!
Hi, With reference to the previous measures and sample data, can we create a measure instead of selecting from the filter option as earlier. I mean, below mentioned results are expected from measure instead of selecting from the filter. I tried to get the same results without filter, but I failed to do so
Thank you
Hi, Its not clear what the question is. Can you rephrase?
btw measure was not updated yet:
Measure2 = SUMX(SUMMARIZE('Table','Table'[Customer],'Table'[Class]), if (CALCULATE(DISTINCTCOUNT('Table'[Y/N?]), ALL('Table'[Class])) = 2,CALCULATE(SUM('Table'[Amount])),BLANK()))
Proud to be a Super User!
Awesome Keyboard Shortcusts in Power BI, thumbs up if you like the article
My Community Blog Articles (check them out!)
My Blog - Power M code to automatically detect column types -
How to create test data using DAX!
Hi Steve, Thank you for your support.
I am able to get the solution with the earlier messages and it is working fine with the filter option. When I needed the selective results from the column of [Y/N?] like "N" only, I added the field [Y/N?] to the filters and it is working fine.
But, I want to get the new(direct) measure for the same results without selecting "N" from the filter option.
Thank you again!
I gues this is what you are looking for:
Measure =
CALCULATE(
SUMX(SUMMARIZE('Table','Table'[Customer],'Table'[Class]), if (CALCULATE(DISTINCTCOUNT('Table'[Y/N?]), ALL('Table'[Y/N?]), ALL('Table'[Class])) = 2,CALCULATE(DISTINCTCOUNT('Table'[OrderID])),BLANK())), 'Table'[Y/N?]="N")
Proud to be a Super User!
Awesome Keyboard Shortcusts in Power BI, thumbs up if you like the article
My Community Blog Articles (check them out!)
My Blog - Power M code to automatically detect column types -
How to create test data using DAX!
User | Count |
---|---|
42 | |
28 | |
23 | |
18 | |
15 |
User | Count |
---|---|
54 | |
35 | |
18 | |
17 | |
15 |