Earn a 50% discount on the DP-600 certification exam by completing the Fabric 30 Days to Learn It challenge.
Hi,
How should the DAX look like if I would like to create measure with following information:
Used columns are:
And values are Amount in EUR.
I would like to calculate the percentage, where the formula is like Status: 1. Execution / Overall Total. For example 28 250 083,14 / 36 057 104,09 = ...
Could you please help me with this?
Best regards,
Kelly
Solved! Go to Solution.
Hi @KellyLen,
Based on your table try to use this measure:
percentage = IF ( DISTINCTCOUNT ( 'Status'[Status] ) > 1 , 1 , IF ( DIVIDE ( CALCULATE ( SUM ( 'Status'[Amount in EUR] ), 'Status'[Status] = VALUES ( 'Status'[Status] ) ), CALCULATE ( SUM ( 'Status'[Amount in EUR] ), ALL ( 'Status'[Status],'Status'[NOK/OK] ) ) ) = BLANK (), 0 , DIVIDE ( CALCULATE ( SUM ( 'Status'[Amount in EUR] ), 'Status'[Status] = VALUES ( 'Status'[Status] ) ), CALCULATE ( SUM ( 'Status'[Amount in EUR] ), ALL ( 'Status'[Status],'Status'[NOK/OK] ) ) ) ) )
Then format as % and you should get the result below (put it as table to be more visible).
If you need to take out only the Ok status and remove the NOK from the calculation
percentage = IF ( MAX ( 'Status'[NOK/OK] ) = "NOK", BLANK (), IF ( DISTINCTCOUNT ( 'Status'[Status] ) > 1, 1, IF ( DIVIDE ( CALCULATE ( SUM ( 'Status'[Amount in EUR] ), 'Status'[Status] = VALUES ( 'Status'[Status] ) ), CALCULATE ( SUM ( 'Status'[Amount in EUR] ), ALL ( 'Status'[Status] ) ) ) = BLANK (), 0, DIVIDE ( CALCULATE ( SUM ( 'Status'[Amount in EUR] ), 'Status'[Status] = VALUES ( 'Status'[Status] ) ), CALCULATE ( SUM ( 'Status'[Amount in EUR] ), ALL ( 'Status'[Status] ) ) ) ) ) )
Total calculations can also be redone to not include NOK
Regards,
MFelix
Regards
Miguel Félix
Proud to be a Super User!
Check out my blog: Power BI em PortuguêsHi @KellyLen,
Suppose your table structure is like below:
Create a measure and add it into Matrix visual.
Percentage = DIVIDE ( MAX ( 'New Measure'[Amount] ), CALCULATE ( SUM ( 'New Measure'[Amount] ), ALLEXCEPT ( 'New Measure', 'New Measure'[Category] ) ) )
Best regards,
Yuliana Gu
Hi,
Actually all the types that have value are OK and rows that do not have type are NOK. I have not got currency in different column, I just have the amounts already in EUR. So the table is like this:
The result you got, seems to be the one I would like to get, but I do not understand the formula you created. And the result definitely has to be in percentage already.
Hi @KellyLen,
Based on your table try to use this measure:
percentage = IF ( DISTINCTCOUNT ( 'Status'[Status] ) > 1 , 1 , IF ( DIVIDE ( CALCULATE ( SUM ( 'Status'[Amount in EUR] ), 'Status'[Status] = VALUES ( 'Status'[Status] ) ), CALCULATE ( SUM ( 'Status'[Amount in EUR] ), ALL ( 'Status'[Status],'Status'[NOK/OK] ) ) ) = BLANK (), 0 , DIVIDE ( CALCULATE ( SUM ( 'Status'[Amount in EUR] ), 'Status'[Status] = VALUES ( 'Status'[Status] ) ), CALCULATE ( SUM ( 'Status'[Amount in EUR] ), ALL ( 'Status'[Status],'Status'[NOK/OK] ) ) ) ) )
Then format as % and you should get the result below (put it as table to be more visible).
If you need to take out only the Ok status and remove the NOK from the calculation
percentage = IF ( MAX ( 'Status'[NOK/OK] ) = "NOK", BLANK (), IF ( DISTINCTCOUNT ( 'Status'[Status] ) > 1, 1, IF ( DIVIDE ( CALCULATE ( SUM ( 'Status'[Amount in EUR] ), 'Status'[Status] = VALUES ( 'Status'[Status] ) ), CALCULATE ( SUM ( 'Status'[Amount in EUR] ), ALL ( 'Status'[Status] ) ) ) = BLANK (), 0, DIVIDE ( CALCULATE ( SUM ( 'Status'[Amount in EUR] ), 'Status'[Status] = VALUES ( 'Status'[Status] ) ), CALCULATE ( SUM ( 'Status'[Amount in EUR] ), ALL ( 'Status'[Status] ) ) ) ) ) )
Total calculations can also be redone to not include NOK
Regards,
MFelix
Regards
Miguel Félix
Proud to be a Super User!
Check out my blog: Power BI em PortuguêsHi,
Actually, I would need the total calculation be based on statuses totals as well.
The hole visual looks actually like this:
I do not know, if the subcategory there changes anything. I did not noticed to add this at first. 😕
I just made a change ot my answer please check it.
Regards
Miguel Félix
Proud to be a Super User!
Check out my blog: Power BI em PortuguêsThank you so much! It really worked! 🙂
Kelly
Hi again,
How to make an easier calculation, where I should only calculate percentage of OK/Grand total?
Best regards,
Kelly
Hi @KellyLen,
Not sure of what you want to calculate can you give an example please.
Regards,
MFelix
Regards
Miguel Félix
Proud to be a Super User!
Check out my blog: Power BI em PortuguêsWhen I have the table only like this:
And I would like to calculate the percentage: OK / Total.
Make and adaptation of the previous formula:
percentage = IF ( DISTINCTCOUNT ( 'Status'[NOK/OK] ) > 1 , 1 , IF ( DIVIDE ( CALCULATE ( SUM ( 'Status'[Amount in EUR] ), 'Status'[NOK/OK] = VALUES ( 'Status'[NOK/OK] ) ), CALCULATE ( SUM ( 'Status'[Amount in EUR] ), ALL ( 'Status'[NOK/OK] ) ) ) = BLANK (), 0 , DIVIDE ( CALCULATE ( SUM ( 'Status'[Amount in EUR] ), 'Status'[NOK/OK] = VALUES ( 'Status'[NOK/OK] ) ), CALCULATE ( SUM ( 'Status'[Amount in EUR] ), ALL ( 'Status'[NOK/OK] ) ) ) ) )
Regards,
MFelix
Regards
Miguel Félix
Proud to be a Super User!
Check out my blog: Power BI em Português@MFelix Thank you, the previous formula worked perfectly as usually. 🙂
I have query with following columns:
And have created tabels like these:
I would like to create measure, which would calculate the percentage 1st pass matching / Matching success.
Are you able to help me.. again? 🙂
Best regards,
Kelly
Hi @KellyLen,
what is the type of data you have in the 1st pass match?
You want to calculate the % based on the total value of that column?
Regards
Miguel Félix
Proud to be a Super User!
Check out my blog: Power BI em PortuguêsHi @MFelix,
I have there values: Count of 1st pass match. Rows are Order origin. and columns are Date.
I would like to calculate % based on Matching success values. For example based on my previous screenshots I would like to calculate Fuel TBO % - 4/68 = 0,0588 .. and like this for each row.
HI @KellyLen,
You need to do something like this:
1st match = DIVIDE ( COUNT ( 'Matching Success'[1st pass matching] ), COUNT ( 'Matching Success'[Order Origin] ) )
Regards,
MFelix
Regards
Miguel Félix
Proud to be a Super User!
Check out my blog: Power BI em Português@MFelix Thanks, it basically works. But I would like exclude from Matching success values with No PO. Is it possible to add it to the formula somehow?
Best regards,
Kelly
How do you get the information that it doesn't ave PO?
Regards
Miguel Félix
Proud to be a Super User!
Check out my blog: Power BI em PortuguêsI have in order origin four values No PO, PO, Crew hotel TBO and Fuel TBO. I would like to exclude the No PO values.
Just filtering works as well. 🙂 Thank you for helping me again. 🙂
Is there anyone that could help me? I would like to know more about creating measures, but I do not understand the logic of it.
User | Count |
---|---|
102 | |
91 | |
87 | |
79 | |
71 |
User | Count |
---|---|
113 | |
105 | |
101 | |
75 | |
64 |